package com.hw.one.dam2.util;

import com.hw.one.dam2.common.DamConstant;

import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * Copyright 2017 汉威.智慧环保事业部.
 * Created by 李亚坤 on 2017/07/22 11:53.
 */
public class SqlServerJDBC {

    private final static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动
    private final static String dbURL = "jdbc:sqlserver://192.168.15.247:1433;DatabaseName=chezaigps_data"; //连接服务器和数据库sample
    private final static String userName = "sa"; //默认用户名
    private final static String userPwd = "scsoft"; //密码
    private static Connection dbConn;

    private static DecimalFormat df_3 = new DecimalFormat("###.######");
    /**
     * 获取sqlserver连接
     */
    private static void getConnection() {
        try {
            Class.forName(driverName);
            dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            System.out.println("SqlServer Connection Successful!");
        }catch (Exception e) {
            e.printStackTrace();
        }

    }


    /**
     * 查找设备位置信息
     * @return
     */
    public static List<Map<String, Object>> findDeviceTrack(String deviceCode, String startTime, String endTime) {
        getConnection();
        try {
            Statement st = dbConn.createStatement();
            String tableName;
            if (startTime != null) {
                tableName = startTime.substring(0, 8).replace("-", "");
            }else {
                tableName = new SimpleDateFormat(DamConstant.NO_SPLIT_SDF_YM).format(new Date());
            }

            String sql = "SELECT [AP_ID]\n" +
                    "      ,[DEV_ID]\n" +
                    "      ,[DEV_CODE]\n" +
                    "      ,[RECDATE]\n" +
                    "      ,[DEV_LNG]\n" +
                    "      ,[DEV_LAT]\n" +
                    "      ,[DEV_VALUE]\n" +
                    "      ,[DEV_UNIT]\n" +
                    "      ,[DEV_SPEED]\n" +
                    "      ,[DEV_DIRECTION]\n" +
                    "      ,[DEV_POWER]\n" +
                    "      ,[DEV_SIGNAL]\n" +
                    "      ,[DEV_GPSNUM]\n" +
                    "      ,[DEV_HAIBA]\n" +
                    "      ,[DEV_RUNTIME]\n" +
                    "      ,[DEV_ADVALUE]\n" +
                    "      ,[DEV_OIL]\n" +
                    "      ,[DEV_ISOUT]\n" +
                    "      ,[DEV_ISSTOP]\n" +
                    "      ,[DEV_ISALARM]\n" +
                    "      ,[DEV_ISAV]\n" +
                    "      ,[DEV_ISHELP]\n" +
                    "  FROM [chezaigps_data].[dbo].[T_CAR_AUTOPOSITION"+tableName+"] where 1=1";
            if (null != deviceCode) {
                sql += " and [DEV_CODE] = '"+deviceCode+"'";
            }
            if (startTime != null && endTime != null) {
                sql += " and [RECDATE] >= '"+startTime+"' and [RECDATE] <= '"+endTime+"'";
            }
            sql += " order by RECDATE ASC";
            ResultSet rs = st.executeQuery(sql);
            List<Map<String, Object>> deviceTrackList = new ArrayList<>();
            Map<String, Object> deviceTrack;
            while (rs.next()) {
                System.out.println("设备编码:" + rs.getString(3) + ";上传时间:" + rs.getString(4) + ";经纬度:" + rs.getString(5) + "," + rs.getString(6));
                deviceTrack = new HashMap<>();
                deviceTrack.put("deviceCode", rs.getString(3));
                deviceTrack.put("recDate", rs.getDate(4) + " " + rs.getTime(4));
                deviceTrack.put("lng", rs.getDouble(5));
                deviceTrack.put("lat", rs.getDouble(6));
                deviceTrackList.add(deviceTrack);
            }
            rs.close();
            st.close();
            dbConn.close();
            return deviceTrackList;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (null != dbConn) {
                try {
                    dbConn.close();
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return new ArrayList<>();
    }


    /**
     * 查找设备位置信息
     * @return
     */
    public static List<Map<String, Object>> findDeviceInfo(Map<String, Object> paramMap) {
        getConnection();
        String deviceCode = (String) paramMap.get("deviceCode");
        String deviceCodeS = (String) paramMap.get("deviceCodeS");
        try {
            Statement st = dbConn.createStatement();
            String sql = "SELECT [DEV_ID]\n" +
                    "      ,[A_ID]\n" +
                    "      ,[ZZJG_ID]\n" +
                    "      ,[DEV_CODE]\n" +
                    "      ,[DEV_PLATENO]\n" +
                    "      ,[DEV_TEL]\n" +
                    "      ,[DEV_CCID]\n" +
                    "      ,[DEV_PORT]\n" +
                    "      ,[DEV_IP]\n" +
                    "      ,[DEV_LNG]\n" +
                    "      ,[DEV_LAT]\n" +
                    "      ,[DEV_VALUE]\n" +
                    "      ,[DEV_UNIT]\n" +
                    "      ,[DEV_SPEED]\n" +
                    "      ,[DEV_DIRECTION]\n" +
                    "      ,[DEV_POWER]\n" +
                    "      ,[DEV_OIL]\n" +
                    "      ,[DEV_SIGNAL]\n" +
                    "      ,[DEV_GPSNUM]\n" +
                    "      ,[DEV_HAIBA]\n" +
                    "      ,[DEV_RUNTIME]\n" +
                    "      ,[DEV_ADVALUE]\n" +
                    "      ,[DEV_ISOUT]\n" +
                    "      ,[DEV_ISSTOP]\n" +
                    "      ,[DEV_ISALARM]\n" +
                    "      ,[DEV_ISHELP]\n" +
                    "      ,[DEV_ISAV]\n" +
                    "      ,[RECDATE]\n" +
                    "  FROM [chezaigps_data].[dbo].[T_CAR_DEVICE] where 1=1";
            if (deviceCode != null && !"".equals(deviceCode)) {
                sql += " and [DEV_CODE] ='"+deviceCode+"'";
            }
            if (deviceCodeS != null && !"".equals(deviceCodeS)) {
                sql += " and [DEV_CODE] in("+deviceCodeS+")";
            }

            ResultSet rs = st.executeQuery(sql);
            List<Map<String, Object>> deviceTrackList = new ArrayList<>();
            Map<String, Object> deviceTrack;

            while (rs.next()) {
                System.out.println("设备编码:" + rs.getString(4) + ";上传时间:" + rs.getDate(28).toString() + " " + rs.getTime(28).toString() +
                        ";经纬度:" + rs.getString(10) + "," + rs.getString(11));
                deviceTrack = new HashMap<>();
                deviceTrack.put("deviceCode", rs.getString(4));
                deviceTrack.put("recDate", rs.getDate(28).toString() + " " + rs.getTime(28).toString());
                deviceTrack.put("lng", df_3.format(rs.getDouble(10)));
                deviceTrack.put("lat", df_3.format(rs.getDouble(11)));
                deviceTrackList.add(deviceTrack);
            }
            rs.close();
            st.close();
            dbConn.close();
            return deviceTrackList;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (null != dbConn) {
                try {
                    dbConn.close();
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return new ArrayList<>();
    }



}
